﻿using MockPrjTeam1.Entity;
using SD.Commons.Helpers;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace MockPrjTeam1.DAO
{
    public class GovOfficeDAO
    {
        public List<GovOffice> GetActive()
        { 
            var list = new List<GovOffice>();

            var results = SqlHelper.ExecuteReader("GetActiveGovOffice");

            while (results.Read())
            {
                var item = new GovOffice();
                item.ID = results.GetInt32(0);
                item.Name = results.GetString(1);
                item.Description = results.GetString(2);
                item.County = "";
                item.Active = results.GetBoolean(3);

                var temp = new Collection<SqlParameter>();
                temp.Add(new SqlParameter("p1", item.ID));
                var result = SqlHelper.ExecuteReader("GetCounty", temp);
                while (result.Read())
                    item.County += result.GetString(0) + " ";

                list.Add(item);
            }

            return list;
        }

        public List<GovOffice> GetAll()
        {
            var list = new List<GovOffice>();

            var results = SqlHelper.ExecuteReader("GetAllGovOffice");

            while (results.Read())
            {
                var item = new GovOffice();
                item.ID = results.GetInt32(0);
                item.Name = results.GetString(1);
                item.Description = results.GetString(2);
                item.County = "";
                item.Active = results.GetBoolean(3);

                var temp = new Collection<SqlParameter>();
                temp.Add(new SqlParameter("p1", item.ID));
                var result = SqlHelper.ExecuteReader("GetCounty", temp);
                while (result.Read())
                    item.County += result.GetString(0) + " ";

                list.Add(item);
            }

            return list;
        }

        public GovOffice GetOne(int id)
        {
            var govOffice = new GovOffice();
            govOffice.County = "";

            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", id));
            var results = SqlHelper.ExecuteReader("GetCounty", temp);
            while (results.Read())
                govOffice.County += results.GetString(0) + " ";

            var temp1 = new Collection<SqlParameter>();
            temp1.Add(new SqlParameter("p1", id));

            var result = SqlHelper.ExecuteReader("GetOneGovOffice", temp1);
            if (result.Read())
            {
                govOffice.ID = result.GetInt32(0);
                govOffice.Name = result.GetString(1);
                govOffice.Description = result.GetString(2);
                govOffice.Active = result.GetBoolean(3);
            }
            return govOffice;
        }

        public bool CheckName(string name, int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", name));
            temp.Add(new SqlParameter("p2", id));
            var result = SqlHelper.ExecuteReader("CheckGovOfficeName", temp);
            if (!result.Read())
                return true;
            else
                return false;
        }

        public bool Edit(GovOffice entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p0", entity.ID));
            temp.Add(new SqlParameter("p1", entity.Name));
            temp.Add(new SqlParameter("p2", entity.Description));
            temp.Add(new SqlParameter("p3", entity.Active));
            var result = SqlHelper.ExecuteNonQuery("EditGovOffice", temp);
            return true;
        }

        public bool Create(GovOffice entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", entity.Name));
            temp.Add(new SqlParameter("p2", entity.Description));           
            temp.Add(new SqlParameter("p3", entity.Active));
            var result = SqlHelper.ExecuteNonQuery("CreateGovOffice", temp);
            return true;
        }

        public List<GovOffice> Filter(int filterId, int isActive)
        {
            var list = new List<GovOffice>();

            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", filterId));
            temp.Add(new SqlParameter("p2", isActive));

            var results = SqlHelper.ExecuteReader("FilterGovOffice", temp);

            while (results.Read())
            {
                var item = new GovOffice();
                item.ID = results.GetInt32(0);
                item.Name = results.GetString(1);
                item.Description = results.GetString(2);
                item.County = "";
                item.Active = results.GetBoolean(3);

                var temp1 = new Collection<SqlParameter>();
                temp1.Add(new SqlParameter("p1", item.ID));
                var result = SqlHelper.ExecuteReader("GetCounty", temp1);
                while (result.Read())
                    item.County += result.GetString(0) + " ";

                list.Add(item);
            }

            return list;
        }
    }
}